#!/usr/bin/php
<?php


  /* ************************************************************************************************
     Copyright (c) 2009-2011
     XCast Labs

     This work is licensed under the Creative Commons Attribution License.

     To view a copy of this license, visit http://creativecommons.org/licenses/by/3.0

     ****************************************************************

     You are free:

         * To Share -- to copy, distribute and transmit the work
         * To Remix -- to adapt the work
         * to make commercial use of the work

     Under the following conditions:

         Attribution -- You must attribute the work in the manner specified by the author
	 or licensor (but not in any way that suggests that they endorse you or your use
	 of the work).

     With the understanding that:

	Waiver --  Any of the above conditions can be waived if you get permission from the copyright holder.

	Public Domain -- Where the work or any of its elements is in the public domain under
	applicable law, that status is in no way affected by the license.

	Other Rights -- In no way are any of the following rights affected by the license:

	    ** Your fair dealing or fair use rights, or other applicable copyright exceptions and limitations;

	    ** The author's moral rights;

	    ** Rights other persons may have either in the work itself or in how the work is used,
	       such as publicity or privacy rights.

     Notice -- For any reuse or distribution, you must make clear to others the license terms
	       of this work. The best way to do this is with a link to this web page.     

   ************************************************************************************************ */


  // $Revision: 23 $
  // $Author: ecaplan@xcastlabs.com $
  // $Date: 2011-08-19 21:36:23 +0000 (Fri, 19 Aug 2011) $
  // $Id: xcf_onemin.php 23 2011-08-19 21:36:23Z ecaplan@xcastlabs.com $


  ////////////////////////////////////////////////////////////////////////////////////////////////
  //
  // XCast Labs Fraud alert.  This is expected to be run every minute via cron.
  // WARNING: All timestamps use GMT.
  // 
  // For more information see the project's home: http://code.google.com/p/xcast-voip-fraud/
  // Especially the project's wiki: http://code.google.com/p/xcast-voip-fraud/w/list
  // 
  ////////////////////////////////////////////////////////////////////////////////////////////////

  ////////////////////////////////////////////////////////////////////////////////////////////////
  // CHANGE LOG:
  // ===========
  // 2011-08-18: eddie (Eddie Caplan) -- fix statistic bug handling historic cost of ZERO: leads
  //                                     to divide-by-zero in calculation of z-values.
  //                                  -- added FRAUD_EXCLUDE_FRAUDDAYS logic
  // 2011-08-11: eddie (Eddie Caplan) -- Prepared for public domain usage
  // 2011-07-13: eddie (Eddie Caplan) -- Created
  ////////////////////////////////////////////////////////////////////////////////////////////////




  ////////////////////////////////////////////////////////////////////////////////////////////////
  // 
  // The following parameters set the thresholds for what call traffic triggers a fraud alert:
  //
  // FRAUD_THRESHOLD: The amount of cost expended terminating traffic to a potentially fraudulent destination.
  //    That is, the traffic may seem anomalous, but if its costs don't exceed $50 in one day, then there is
  //    no need to report it.
  //
  //
  // FRAUD_ZVAL_30DAY: The statistical Z-Value that triggers an alert... based on the past 30 days of traffic.
  //    See http://en.wikipedia.org/wiki/Standard_score
  //
  //
  // FRAUD_ZVAL_7DAY: The statistical Z-Value that triggers an alert... based on the past 7 days of traffic.
  //
  //
  // FRAUD_DRILLDOWN_HIGHPCT: It matters how much of the anomolous traffic was produced by a single account.
  //    That is, a high spike in traffic from a variety of sources more likely indicates a special event
  //    (e.g. national holiday) in the destination country.  However, if the spike is concentrated from
  //    a single account, this is more likely fraud.
  //
  //    So, FRAUD_DRILLDOWN_HIGHPCT indicates how much (in %) of the traffic a single account must be
  //    responsible for before it is reported as a source of fraud.
  //
  ////////////////////////////////////////////////////////////////////////////////////////////////
define("FRAUD_THRESHOLD", 50);			// $50 per route
define("FRAUD_ZVAL_30DAY", 7.0);		// 7 standard deviations
define("FRAUD_ZVAL_7DAY", 7.0);			// 7 standard deviations
define("FRAUD_DRILLDOWN_HIGHPCT", 20.0);	// 20% or more


////////////////////////////////////////////////////////////////////////////////////////////////
// Better statistics exclude days previously reported as fraud.  Otherwise a history of fraud
// to a destination would change the baseline up to a "false normal".  Setting this to TRUE
// excludes previous fraud.
////////////////////////////////////////////////////////////////////////////////////////////////
define("FRAUD_EXCLUDE_FRAUDDAYS", TRUE);	// exclude days reported as fraud from summaries?



echo "[".date('Y.m.d - H:i:s',time())."] ================================ starting up: ".print_r($argv,true)."\n";
$PHPFILE = basename(__FILE__);




////////////////////////////////////////////////////////////////////////////////////////////////
// because cron runs us every minute, it's possible that some delay in finishing in that minute
// may cause another instance of us to try to run again before we are done.
////////////////////////////////////////////////////////////////////////////////////////////////
echo "[".date('Y.m.d - H:i:s',time())."] $PHPFILE process count: ";
$processes = system("export TERM=xterm;/bin/ps axf | /bin/grep $PHPFILE | /bin/grep -v grep | grep -v \"/bin/sh\" |  /usr/bin/wc -l");
if ( $processes > "1") {
    print "Another process in memory. Exiting ...\n";
    exit(2);
}




////////////////////////////////////////////////////////////////////////////////////////////////
//
// Replace database definitions with your own db info.
//
////////////////////////////////////////////////////////////////////////////////////////////////
$db_host = "the host";
$db_db   = "the database";
$db_user = "the user";
$db_pwd  = "the password";
$dbconn = mysql_connect($db_host,$db_user,$db_pwd) or die ("Could not connect to $db_host as $db_user");
mysql_select_db($db_db, $dbconn) or die ("Could not select DB $db_db");




function showusage($argv, $argc) {
    echo "Usage $argv[0] [<starting_date>] [DEBUG]\n";
    echo "    Where the OPTIONAL <starting_date>, in YYYY-MM-DD format, indicates when to\n";
    echo "    begin looking for fraud.  This script will then catch up to the current\n";
    echo "    day/time (in GMT!) and then exit.\n";
    echo "\n";
    echo "    Where the OPTIONAL value [DEBUG] will turn on debugging.  More output will\n";
    echo "    be shown and the the fraud reports will only be sent to the debugging team.\n";
    echo "\n";
    echo "    Example: $argv[0] \"2011-07-13\" DEBUG\n"; 
    exit(2);
}
if ($argc > 3) {
    echo "\n??? Wrong number of arguments.\n\n";
    showusage($argv,$argc);
    exit(2);
}
$DEBUG = false;
$argv_dt = NULL;
foreach ($argv as $argk => $arg) {
    if ($argk == 0) continue;
    if ($arg=="DEBUG") $DEBUG=TRUE;
    else {
	list($yr, $mn, $dt) = sscanf($arg, "%4d-%2d-%2d");
	if (!checkdate($mn, $dt, $yr)) {
	    echo "\n?? Invalid date format: $arg\n\n";
	    showusage($argv, $argc);
	    exit(2);
	}
	$argv_dt=$argv[1];
    }
}
if ($argv_dt == NULL) {
    // use current date (GMT)
    $qu30 = "select substring(utc_timestamp(),1,10) as dt";
    //echo "[".date('Y.m.d - H:i:s',time())."] qu30 = $qu30\n";
    $q30 = mysql_query($qu30, $dbconn);
    $r30 = mysql_fetch_array($q30);
    $argv_dt = $r30['dt'];
}
//echo "argv_dt = $argv_dt\n";




if ($DEBUG) echo "[".date('Y.m.d - H:i:s',time())."] ================================ DEBUGGING IS TURNED ON\n";




////////////////////////////////////////////////////////////////////////////////////////////////
// All dates BEFORE today will be summarized from 00:00:00 thru 23:59:59 (GMT).
//
// Today's date will be summarized for the past 24 hours (GMT).  Subsequent runs of this script each
// minute will update the fraud data with the moving 24-hour window until we cross midnight, leaving
// the day's summary pretty close (but not perfect) to a summary from 00:00:00 thru 23:59:59, inclusive.
//
// After that, the fraud checks will be done for the past dates thru the current date/time.
////////////////////////////////////////////////////////////////////////////////////////////////




// STEP 1: Start by backfilling summary data up to today.
$datelabel = $argv_dt;

$qu9 = "select '$datelabel 00:00:00' + interval 1 day as firstdate";
//echo "[".date('Y.m.d - H:i:s',time())."] qu9 = $qu9\n";
$q9 = mysql_query($qu9, $dbconn);
$r9 = mysql_fetch_array($q9);
$thisdt = $r9['firstdate'];

$qu2 = "select '$PHPFILE' as phpfile, substring(utc_timestamp(),1,10) as limitdt";
//echo "[".date('Y.m.d - H:i:s',time())."] qu2 = $qu2\n";
$q2 = mysql_query($qu2, $dbconn);
$r2 = mysql_fetch_array($q2);
$limitdt = $r2['limitdt'];

//echo "thisdt=$thisdt, datelabel=$datelabel, limitdt=$limitdt\n";
//exit;

while ($datelabel < $limitdt) {
    echo "[".date('Y.m.d - H:i:s',time())."] STEP 1: backfilling traffic summary up to thisdt=$thisdt, datelabel=$datelabel\n";

    $cdrtot = $costtot = 0;
    $fulldata = fill_fulldata($thisdt, $datelabel, $cdrtot, $costtot);
    if ($cdrtot > 0) {
	$ins = $upd = $same = 0;
	insupd_db($fulldata, $ins, $upd, $same);
	echo "[".date('Y.m.d - H:i:s',time())."] STEP 1: backfill: thisdt=$thisdt, $ins inserted, $upd updated, $same unchanged\n";
    } else {
	echo "[".date('Y.m.d - H:i:s',time())."] STEP 1: backfill: cdrtot=$cdrtot, costtot=$costtot for backfilling up to thisdt=$thisdt, datelabel=$datelabel -- DID NOT INSERT/UPDATE xcf_traffic_summary table.\n";
    }

    $qu8 = "select '$thisdt' + interval 1 day as newdate";
    //echo "[".date('Y.m.d - H:i:s',time())."] qu8 = $qu8\n";
    $q8 = mysql_query($qu8, $dbconn);
    $r8 = mysql_fetch_array($q8);
    $thisdt = $r8['newdate'];

    $qu9 = "select substring('$datelabel 00:00:00' + interval 1 day,1,10) as nextdate";
    //echo "[".date('Y.m.d - H:i:s',time())."] qu9 = $qu9\n";
    $q9 = mysql_query($qu9, $dbconn);
    $r9 = mysql_fetch_array($q9);
    $datelabel = $r9['nextdate'];
}




// STEP 2: fill summary data for today
$qu5 = "select '$PHPFILE' as phpfile, utc_timestamp() as nowdt";
//echo "qu5 = $qu5\n";
$q5 = mysql_query($qu5, $dbconn);
$r5 = mysql_fetch_array($q5);
$nowdt = $r5['nowdt'];
$datelabel = substr($nowdt,0,10);

if ($DEBUG) {
    echo "[".date('Y.m.d - H:i:s',time())."] STEP 2: summarizing today to $nowdt\n";
}

$cdrtot = $costtot = 0;
$fulldata = fill_fulldata($nowdt, $datelabel, $cdrtot, $costtot);
if ($cdrtot > 0) {
    $ins = $upd = $same = 0;
    insupd_db($fulldata, $ins, $upd, $same);
    echo "[".date('Y.m.d - H:i:s',time())."] STEP 2: summarized nowdt=$nowdt, $ins inserted, $upd updated, $same unchanged\n";
} else {
    if ($DEBUG) {
	echo "[".date('Y.m.d - H:i:s',time())."] STEP 2: summarized cdrtot=$cdrtot, costtot=$costtot for today's fill date '$nowdt' -- DID NOT INSERT/UPDATE xcf_traffic_summary table.\n";
    }
}




// STEP 3: check for fraud for previous days
$datelabel = $argv_dt;

$qu9 = "select '$datelabel 00:00:00' + interval 1 day as firstdate";
//echo "[".date('Y.m.d - H:i:s',time())."] qu9 = $qu9\n";
$q9 = mysql_query($qu9, $dbconn);
$r9 = mysql_fetch_array($q9);
$thisdt = $r9['firstdate'];

$qu2 = "select '$PHPFILE' as phpfile, substring(utc_timestamp(),1,10) as limitdt";
//echo "[".date('Y.m.d - H:i:s',time())."] qu2 = $qu2\n";
$q2 = mysql_query($qu2, $dbconn);
$r2 = mysql_fetch_array($q2);
$limitdt = $r2['limitdt'];

//echo "thisdt=$thisdt, datelabel=$datelabel, limitdt=$limitdt\n";
// exit;

while ($datelabel < $limitdt) {

    echo "[".date('Y.m.d - H:i:s',time())."] STEP 3: backfill: checking for fraud with thisdt=$thisdt, datelabel=$datelabel\n";
    $fcresult = fraudcheck($thisdt, $datelabel, FRAUD_THRESHOLD, FRAUD_ZVAL_30DAY, FRAUD_ZVAL_7DAY);

    $ins = $upd = 0;
    $inserted = record_suspicions($thisdt, $datelabel, $fcresult, $ins, $upd);
    //echo "inserted = ".print_r($inserted,true)."\n";
    if ($DEBUG) {
	// even when debugging, only send reports FOR BACKFILL if there is fraud to report
	if (count($inserted) > 0) {
	    report_suspicions($thisdt, $datelabel, $fcresult, $inserted);
	}
    }

    $qu8 = "select '$thisdt' + interval 1 day as newdate";
    //echo "[".date('Y.m.d - H:i:s',time())."] qu8 = $qu8\n";
    $q8 = mysql_query($qu8, $dbconn);
    $r8 = mysql_fetch_array($q8);
    $thisdt = $r8['newdate'];

    $qu9 = "select substring('$datelabel 00:00:00' + interval 1 day,1,10) as nextdate";
    //echo "[".date('Y.m.d - H:i:s',time())."] qu9 = $qu9\n";
    $q9 = mysql_query($qu9, $dbconn);
    $r9 = mysql_fetch_array($q9);
    $datelabel = $r9['nextdate'];
}




// STEP 4: check for fraud with current time, GMT!
$qu2 = "select '$PHPFILE' as phpfile, utc_timestamp() as startdt";
//echo "[".date('Y.m.d - H:i:s',time())."] qu2 = $qu2\n";
$q2 = mysql_query($qu2, $dbconn);
$r2 = mysql_fetch_array($q2);
$startdt = $r2['startdt'];

$qu7 = "select '$PHPFILE' as phpfile, utc_timestamp() as thisdt";
//echo "[".date('Y.m.d - H:i:s',time())."] qu7 = $qu7\n";
$q7 = mysql_query($qu7, $dbconn);
$row7 = mysql_fetch_assoc($q7);
$thisdt = $row7['thisdt'];
$datelabel = substr($thisdt,0,10);

if ($DEBUG) {
    echo "[".date('Y.m.d - H:i:s',time())."] STEP 4: checking thisdt=$thisdt\n";
}

$fcresult = fraudcheck($thisdt, $datelabel, FRAUD_THRESHOLD, FRAUD_ZVAL_30DAY, FRAUD_ZVAL_7DAY);
//echo "count(fsresult) == ".count($fcresult)."\n";
//echo "fcresult = ".print_r($fcresult,true)."\n";

$ins = $upd = 0;
$inserted = record_suspicions($thisdt, $datelabel, $fcresult, $ins, $upd);
//echo "inserted = ".print_r($inserted,true)."\n";
report_suspicions($thisdt, $datelabel, $fcresult, $inserted);

if (count($fcresult['data']) > 0) {
    echo "[".date('Y.m.d - H:i:s',time())."] STEP 4: thisdt=$thisdt, datelabel=$datelabel, fraud detected=".count($fcresult['data']).", inserted=$ins, updated=$upd\n";
}




mysql_close($dbconn);
if ($DEBUG) {
    echo "[".date('Y.m.d - H:i:s',time())."] NORMAL EXIT\n";
}
exit(0);




////////////////////////////////////////////////////////////////////////////////////////////////
//
// utility functions
//
////////////////////////////////////////////////////////////////////////////////////////////////
function get_routearr() {
    GLOBAL $db_host, $db_db, $db_user, $db_pwd;
    GLOBAL $dbconn;
    GLOBAL $PHPFILE;

    $retarr = array();
    $qu1 = "select distinct route from xcf_routes";
    //echo $qu1."\n";
    $q1 = mysql_query($qu1, $dbconn);
    while ($r1 = mysql_fetch_array($q1)) {
	$retarr[$r1['route']] = $r1['route'];
    }

    return($retarr);
}





//
// Fill in $fulldata with actual call info.
//
// Note:
//    $date is the datetime LIMIT, NON-INCLUSIVE to summarize, starting 1 day earlier
//    $datelabel is the YYYY-MM-DD string to use for the summarized data.
//
// This may seem clumsy, but logical.  Viz:
//
// Example 1: When we are backfilling, we summarize 2011-01-01's data ending 2011-01-02 (non-inclusive).
//    e.g. $date is 2011-01-02 00:00:00
//         $datelabel is 2011-01-01
//
// Example 2: When we are filling today's data, we summarize (for example) 2011-01-01 11:00:00 through 2011-01-02 10:59:59
//    e.g. $date is 2011-01-02 11:00:00
//         $datalabel is 2011-01-02
//
function fill_fulldata($date, $datelabel, &$cdrtot, &$costtot) {
    GLOBAL $db_host, $db_db, $db_user, $db_pwd;
    GLOBAL $dbconn;
    GLOBAL $PHPFILE;

    $retval = array();
    $cdrtot = $costtot = 0;

    // always refill routearr because the routes can change
    $routearr = get_routearr();
    foreach ($routearr as $route) {
	$retval[$datelabel][$route] = array('route' => $route, 'cost' => 0);
    }

    ////////////////////////////////////////////////////////////////////////////////////////////////
    // Process the CDR
    // Take pity on the server, collect only 2 minutes' worth of CDR at a time
    ////////////////////////////////////////////////////////////////////////////////////////////////
    $qu6 = "select '$date' - interval 1 day as startdt";
    //echo "[".date('Y.m.d - H:i:s',time())."] qu6 = $qu6\n";
    $q6 = mysql_query($qu6, $dbconn);
    $row6 = mysql_fetch_assoc($q6);
    $startdt = $row6['startdt'];
    $limitdt = $date;
    
    $timeincr = 0;
    $timedelta = 2;	// 2 minutes
    do {
	$qu7 = "select '$PHPFILE' as phpfile,
			'$startdt' + interval ".($timeincr*$timedelta)." minute as thisdt,
			'$startdt' + interval ".(($timeincr+1)*$timedelta)." minute as nextdt";
	//echo "[".date('Y.m.d - H:i:s',time())."] qu7 = $qu7\n";
	$q7 = mysql_query($qu7, $dbconn);
	$row7 = mysql_fetch_assoc($q7);
	$thisdt = $row7['thisdt'];
	$nextdt = $row7['nextdt'];
	$timeincr++;

	//echo ".";

	// NOTE: get unsuccessful calls too so $cdrtot gives an accurate count of all CDRs processed.  That's
	//       needed to ensure we stop when no more cdr are available.
	//
	// NOTE: ignore routes 0, 1538, 1624 -- that is, ignore domestic routes, only pay look for international fraud
	$qu3 = "select route, sum(cost) as cost, count(*) as cdrcnt, round(sum(billedamt),6) as billedamt,
			'$PHPFILE' as phpfile
		    from xcf_cdr
		    where calldate  >= '$thisdt' and calldate  < '$nextdt'
		      and route not in (0, 1538, 1624)
		    group by route
		    order by route";
	//echo "qu3 = $qu3\n";
	$q3 = mysql_query($qu3, $dbconn);
	while ($r3 = mysql_fetch_array($q3)) {
	    $cdrtot  += $r3['cdrcnt'];
	    $costtot += $r3['cost'];

	    if (!isset($retval[$datelabel][$r3['route']])) {
		echo "[".date('Y.m.d - H:i:s',time())."] fill_fulldata: unexpected route=".$r3['route']." returned between $startdt and $nextdt\n";
		print_r($retval[$datelabel]);
		exit;
	    } else {
		$retval[$datelabel][$r3['route']]['cost'] += $r3['cost'];
	    }
	}
    } while ($nextdt < $limitdt);

    //echo "\n";
    //echo "fill_fulldata: startdt=$startdt, limitdt=$limitdt, retval=".print_r($retval,true)."\n";

    return($retval);
}




function insupd_db($fulldata, &$ins, &$upd, &$same) {
    GLOBAL $db_host, $db_db, $db_user, $db_pwd;
    GLOBAL $dbconn;
    GLOBAL $PHPFILE;

    $ins = $upd = $same = 0;
    foreach ($fulldata as $date => $routes) {
	//echo "[".date('Y.m.d - H:i:s',time())."] processing date '$date'\n";

	//echo "[".date('Y.m.d - H:i:s',time())."] updating xcf_traffic_summary\n";
	foreach ($routes as $route => $v) {
	    $qu = "select * from xcf_traffic_summary where date='$date' and route=$route";
	    //echo "[".date('Y.m.d - H:i:s',time())."] qu = $qu\n";
	    $q = mysql_query($qu, $dbconn);
	    if (mysql_num_rows($q) == 0) {
		$qu4 = "insert into xcf_traffic_summary set
			    date='$date',
			    route=$route,
			    cost=".round($fulldata[$date][$route]['cost'],3);
		//echo $qu4."\n";
		if ($q4=mysql_query($qu4, $dbconn) === FALSE) {
		    echo "[".date('Y.m.d - H:i:s',time())."] ? cannot insert/update record: ".$qu4."\n";
		    echo "[".date('Y.m.d - H:i:s',time())."] ? nevertheless: date=$date, $ins inserted, $upd updated, $same unchanged\n";
		    exit(1);
		}
		$ins++;
	    } else {
		$row = mysql_fetch_array($q);
		if (round($row['cost'],3) != round($fulldata[$date][$route]['cost'],3)) {
		    //echo "[".date('Y.m.d - H:i:s',time())."] round(row['cost'],3)=".round($row['cost'],3).", round(fulldata[$date][$route]['cost'],3)=".round($fulldata[$date][$route]['cost'],3)."\n";
		    $qu4 = "update xcf_traffic_summary set
				date='$date',
				route=$route,
				cost=".round($fulldata[$date][$route]['cost'],3)."
			      where date='$date' and route=$route";
		    //echo $qu4."\n";
		    if ($q4=mysql_query($qu4, $dbconn) === FALSE) {
			echo "[".date('Y.m.d - H:i:s',time())."] ? cannot insert/update record: ".$qu4."\n";
			echo "[".date('Y.m.d - H:i:s',time())."] ? nevertheless: date=$date, $ins inserted, $upd updated, $same unchanged\n";
			exit(1);
		    }
		    $upd++;
		} else {
		    $same++;
		}
	    }
	}
    }
}




////////////////////////////////////////////////////////////////////////////////////////////////
// record this suspicious activity
//
// fraudcheck has done all the research.  We just need to store the results.
//
// RETURNS: An array that indicates which ($datelabel, $route, and $account) combinations were
//          NEW, that we INSERTED into xcf_results
////////////////////////////////////////////////////////////////////////////////////////////////
function record_suspicions($thisdt, $datelabel, $fcresult, &$ins, &$upd) {
    GLOBAL $db_host, $db_db, $db_user, $db_pwd;
    GLOBAL $dbconn;
    GLOBAL $PHPFILE;
    GLOBAL $DEBUG;

    $retval = array();

    //echo "fcresult= ".print_r($fcresult,true)."\n";
    foreach ($fcresult['data'] as $route => $routedata) {
	foreach ($routedata['drilldown'] as $account => $acctdata) {

	    ////////////////////////////////////////////////////////////////////////////////////////////////
	    //
	    // EXCEPTION #2:  If an ROUTE/account was reported less than 24 hours ago, only update don't insert.
	    //                (Well, fudge it actually to 24 hours + 10 minutes to handle slop)
	    //
	    //    This will fail to record (and report) fraud that occurs in separate spikes during one 24-hour period.  That is,
	    //    somebody may see one spike, but not notice the 2nd because we reported it only once.  This fraud report only
	    //    alerts people to pay attention to a route and/or account.  It doesn't replace good human common sense and vigilence.
	    //
	    //    NOTE: The following query intentionally ignores the "isfraud" flag.  We don't care here whether the last report
	    //          turned out to actually be fraud or not.  We only care whether we reported it recently or not.
	    //
	    ////////////////////////////////////////////////////////////////////////////////////////////////
	    $newfraud = TRUE;
	    $frid = 0;
	    $frcdt = NULL;

	    $qu10 = "select * from xcf_results where route=$route and account='$account' and dateCreated >= utc_timestamp() - interval (24*60)+10 minute";
	    //echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: qu10 = $qu10\n";
	    $q10 = mysql_query($qu10, $dbconn);
	    if (mysql_num_rows($q10) > 0) {
		$r10 = mysql_fetch_array($q10);
		echo "[".date('Y.m.d - H:i:s',time())."] EXCLUSION #2: %%%%%%%%%%%%%%%% route=$route, account='$account' was reported recently: dateCreated=".$r10['dateCreated']."\n";
		$newfraud = FALSE;
		$frid = $r10['id'];
		$frcdt = $r10['dateCreated'];
	    } 


	    if ($newfraud) {

		array_push($retval, array('datelabel'=>$datelabel, 'route'=>$route, 'account'=>$account));

		$qu2 = "insert into xcf_results set
			    date='$datelabel',
			    route='$route',
			    account='$account',
			    routename='"             .$routedata['routename']."',
			    route_cost='"            .$routedata['route_cost']."',
			    route_cost_zval7='"      .$routedata['route_cost_zval7']."',
			    route_cost_zval30='"     .$routedata['route_cost_zval30']."',
			    account_calls='"         .$acctdata['account_calls']."',
			    account_minutes='"       .$acctdata['account_minutes']."',
			    account_cost='"          .$acctdata['account_cost']."',
			    account_billedamt='"     .$acctdata['account_billedamt']."',
			    account_maxcalldate='"   .$acctdata['account_maxcalldate']."',
			    isfraud=1,
			    comment=NULL,
			    dateCreated=utc_timestamp(),
			    dateUpdated=utc_timestamp()";
		$ins++;

		echo "[".date('Y.m.d - H:i:s',time())."] ################ NEW FRAUD: datelabel=$datelabel, route=$route, ".$routedata['routename'].", $account: ".$acctdata['account_cost']."\n";

	    } else {
		// update the changeable values -- NOTE THAT WE DO NOT CHANGE THE date OR isfraud COLUMNS!!!!!
		$qu2 = "update xcf_results set
			    routename='"             .$routedata['routename']."',
			    route_cost='"            .$routedata['route_cost']."',
			    route_cost_zval7='"      .$routedata['route_cost_zval7']."',
			    route_cost_zval30='"     .$routedata['route_cost_zval30']."',
			    account_calls='"         .$acctdata['account_calls']."',
			    account_minutes='"       .$acctdata['account_minutes']."',
			    account_cost='"          .$acctdata['account_cost']."',
			    account_billedamt='"     .$acctdata['account_billedamt']."',
			    account_maxcalldate='"   .$acctdata['account_maxcalldate']."',
			    dateUpdated=utc_timestamp()
			where id=$frid";
		$upd++;

		echo "[".date('Y.m.d - H:i:s',time())."] ................ REDETECTED FRAUD: dateCreated=$frcdt, datelabel=$datelabel, route=$route, ".$routedata['routename'].", $account: ".$acctdata['account_cost']."\n";

	    }

	    //echo "[".date('Y.m.d - H:i:s',time())."] qu2 = ".$qu2."\n";
	    if ($q2=mysql_query($qu2, $dbconn) === FALSE) {
		echo "[".date('Y.m.d - H:i:s',time())."] ? cannot insert/update record: ".$qu2."\n";
		echo "[".date('Y.m.d - H:i:s',time())."] ? nevertheless: $ins inserted, $upd updated\n";
		exit(1);
	    }
	}
    }


    return($retval);
}




////////////////////////////////////////////////////////////////////////////////////////////////
//
// Report fraud results to interested parties:
//
// 1: large group who want to hear about NEW fraud suspicions
// 2: small group who want to hear once a day that the fraud system is running
//
//
// SAMPLE EMAIL:
//
//	REALTIME Fraud Report run ending 2011-08-03 00:00:00 GMT
//
//	Destinations whose costs are anomalous and > $50:
//
//	                                      Avg    Avg
//	  Destination        Route   08-03  Last7 Last30  08-02  08-01  07-31  07-30  07-29  07-28  07-27   ZVal7  ZVal30
//	  ===============================================================================================================
//	  Sierra Leone        1455 2598.22   7.90   1.84 2598.22  55.31   0.00   0.00   0.00   0.00   0.00  133.83  261.49
//
//
//
//	Drilldown by route / account:
//
//	  Route  Account         Calls         Minutes   BilledAmount     Cost   %Cost  LastCallDate
//	  ======================================================================================================
//	      1455  75354             584          7297.1        2569.10  2598.22   100.0  2011-08-02 01:42:27 GMT
//
//
//
//	Account information:
//
//	    Account: 75354
//	    Name: Customer 75354
//	    Contact: Contact Person
//	    Phone: X-XXX-XXX-XXXX
//	    Email: cp@75354.com
//	    Journals:
//		2011-08-12 : 75354 person 0 : A journal entry about account 75354 by person 0 at date 2011-08-12.
//
//		2011-08-11 : 75354 person 1 : A journal entry about account 75354 by person 1 at date 2011-08-11.
//
//		2011-08-10 : 75354 person 2 : A journal entry about account 75354 by person 2 at date 2011-08-10.
//
////////////////////////////////////////////////////////////////////////////////////////////////
function report_suspicions($thisdt, $datelabel, $fcresult, $inserted) {
    GLOBAL $db_host, $db_db, $db_user, $db_pwd;
    GLOBAL $dbconn;
    GLOBAL $PHPFILE;
    GLOBAL $DEBUG;

    $numfraud = count($inserted);
    $msg = NULL;
    if ($numfraud == 0) {
	//echo "substr($thisdt,10,4) == ".substr($thisdt,13,4)."\n";
	//if (substr($thisdt,13,4)==":00:") {	// once an hour

	// Notify the small group once a day that the fraud system is running
	// "once a day" is defined as when $thisdt's TIME portion is "00:00:xx"
	if ($DEBUG) {
	    echo "substr($thisdt,11,5) == ".substr($thisdt,11,5)."\n";
	}
	if (substr($thisdt,11,5)=="00:00") {	// once per day
	    $msg .= "\nThe fraud system is running.\n";
	    $msg .= "\nNo fraud to report.\n";
	}
    } else {
	// Have new fraud.  Notify the large group
	$msg .= "Destinations whose costs are anomalous and > $".FRAUD_THRESHOLD.":\n\n";
	$msg .= sprintf("  %-18s %5s %7s %6s %6s %6s %6s %6s %6s %6s %6s %6s %7s %7s\n",
			"", "", "", "Avg", "Avg", "", "", "", "", "", "", "", "", "");
	$msg .= sprintf("  %-18s %5s %7s %6s %6s %6s %6s %6s %6s %6s %6s %6s %7s %7s\n",
			"Destination", "Route", substr($fcresult['today'],5,5), "Last7", "Last30",
			substr($fcresult[-1],5,5),
			substr($fcresult[-2],5,5),
			substr($fcresult[-3],5,5),
			substr($fcresult[-4],5,5),
			substr($fcresult[-5],5,5),
			substr($fcresult[-6],5,5),
			substr($fcresult[-7],5,5),
			"ZVal7", "ZVal30");
	$msg .= "  ===============================================================================================================\n";
	foreach ($fcresult['data'] as $route => $routedata) {
	    $msg .= sprintf("  %-18s %5d %7.2f %6.2f %6.2f",
			    substr($routedata['routename'],0,17),
			    $route,
			    $routedata['route_cost'],
			    $routedata['route_cost_avglast7'],
			    $routedata['route_cost_avglast30']);
	    for ($i=-1; $i>=-7; $i--) {
		$msg .= sprintf(" %6.2f", $routedata[$i]);
	    }
	    $msg .= sprintf(" %7.2f %7.2f",
			    $routedata['route_cost_zval7'],
			    $routedata['route_cost_zval30']);
	    $msg .= "\n";
	}
	$msg .= "\n";
	$msg .= "\n";


	// since $numfraud>0, at least one item should match
	$msg .= "\nDrilldown by route / account:\n\n";
	$msg .= sprintf("  %5s  %-13s %7s %15s %14s %8s %7s  %-23s\n",
			"Route", "Account", "Calls", "Minutes", "BilledAmount", "Cost", "%Cost", "LastCallDate"); 
	$msg .= "  ======================================================================================================\n";
	$uniqaccts = array();
	foreach ($fcresult['data'] as $route => $routedata) {
	    if (count($routedata['drilldown']) == 0) {
		$msg .= "  ?? No drilldown data available for $route (this shouldn't be!)\n";
	    } else {
		foreach ($routedata['drilldown'] as $account => $acctdata) {
		    $msg .= sprintf("  %5s  %-13s %7d %15.1f %14.2f %8.2f %7.1f  %-23s\n",
				    $route,
				    $account,
				    $acctdata['account_calls'],
				    $acctdata['account_minutes'],
				    $acctdata['account_billedamt'],
				    $acctdata['account_cost'],
				    $acctdata['account_pctcost'],
				    $acctdata['account_maxcalldate']);

		    // This helps the next section of the report: remember unique accounts
		    $uniqaccts[$account] = $account;
		}
	    }
	}
	$msg .= "\n";
	$msg .= "\n";
	if (count($uniqaccts) == 0) {
	    $msg .= "No account information available!  (This shouldn't be)\n";
	} else {
	    $msg .= "\nAccount information:\n\n";
	    foreach ($uniqaccts as $account => $uav) {

		$ai = fraudaccountinfo($account);
		$msg .= sprintf("  %12s: %s\n  %12s: %s\n  %12s: %s\n  %12s: %s\n  %12s: %s\n  %12s: ",
				"Account",      $account,
				"Name",         $ai['custname'],
				"Contact",	$ai['contactname'],
				"Phone",	$ai['phone'],
				"Email",	$ai['email'],
				"Journals");

		$journals = fraudjournals($account, 3);	// get 3 most recent journal entries for this account
		if (count($journals) == 0) {
		    $msg .= "No Journal Entries Available\n";
		} else {
		    foreach ($journals as $uavjk => $uavjv) {
			//echo "uavjv = ".print_r($uavjv,true)."\n";
			$msg .= "\n";
			$msg .= sprintf("%9s%s : %s : %s\n",
					" ",
					$uavjv['date'],
					$uavjv['who'], 
					// The following reformats:
					//     * no \r or \n
					//     * wrap text to 70 chars
					//     * left margin of 37 spaces (i.e. "      Journals: YYYY-MM-DDDD HH:MMAP : XXX : ")
					//     * break long words in the middle
					wordwrap(str_replace(array("\r","\n")," ",$uavjv['text']),
						 70,
						 "\n".sprintf("%37s"," "),
						 TRUE));
		    }
		    $msg .= "\n";
		}
		$msg .= "\n";
	    }
	}
	$msg .= "\n";
	$msg .= "\n";
    }
    

    // if there is something to send, send it
    if ($msg != NULL) {
	$body = "/tmp/xcf_onemin_alert.txt";
	$fd = fopen($body,'w');
	
	fwrite($fd, "\nREALTIME Fraud Report run ending $thisdt GMT\n\n");
	fwrite($fd, $msg);
	fclose($fd);
	$subj = "\"REALTIME Switch Fraud Report $datelabel (GMT) : $numfraud route".($numfraud==1 ? "" : "s")." \"";
	if ($numfraud > 0) {
	    if ($DEBUG) {
		// send to developers
		$lastline = system("./xcf_file_sender_debug.sh $body $subj ecaplan@xcastlabs.com");
	    } else {
		// send to production fraud response team
		$lastline = system("./xcf_file_sender_debug.sh $body $subj ecaplan@xcastlabs.com");
		//		$lastline = system("./xcf_file_sender.sh $body $subj ecaplan@xcastlabs.com");
	    }
	} else {
	    if ($DEBUG) {
		// send to developers
		$lastline = system("./xcf_file_sender_debug.sh $body $subj ecaplan@xcastlabs.com");
	    } else {
		// send to administrators who want to know the fraud jobs are alive and running
		$lastline = system("./xcf_file_sender_debug.sh $body $subj ecaplan@xcastlabs.com");
		//		$lastline = system("./xcf_file_sender_nofraud.sh $body $subj ecaplan@xcastlabs.com");
	    }
	}
	//echo "[".date('Y.m.d - H:i:s',time())."] lastline = $lastline\n";
	unlink($body);
    }
}




////////////////////////////////////////////////////////////////////////////////////////////////
//
// fraudcheck
// ----------
//
// Takes parameters: 
//	$dt -- the date/time to check for fraud in the format: YYYY-MM-DD HH:MM:SS
//	$datelabel -- the date to call this fraud in format: YYYY-MM-DD
//	$fraud_threshold  -- usually FRAUD_THRESHOLD  -- the dollar amount of cost ABOVE WHICH we look for fraud
//	$fraud_zval_30day -- usually FRAUD_ZVAL_30DAY -- the 30-day statistical zval above which we consider fraud
//	$fraud_zval_7day  -- usually FRAUD_ZVAL_7DAY  -- the 7-day statistical zval above which we consider fraud
//
//
// Returns an array of fraud results BY ROUTE (not broken down by account!)  like the following.
// We return the headers because we've already computed them and they will be helpful for the caller:
//
// $result = ('today'  => '2011-07-06',		// mm-dd of date passed as parameter
//		-1     => '2011-07-05',		// date 1 day ago
//		-2     => '2011-07-04',		// date 2 days ago
//		-3     => '2011-07-03',		// date 3 days ago
//		-4     => '2011-07-02',		// date 4 days ago
//		-5     => '2011-07-01',		// date 5 days ago
//		-6     => '2011-06-30',		// date 6 days ago
//		-7     => '2011-06-29',		// date 7 days ago
//		'data' => array(1456 => array ('routename'            => 'Sierra Leone Freetown',	-- country corresponding to route (array key)
//					       'route_cost'           => 383.57,			-- all cost to this route, regardless of account
//					       'route_cost_avglast7'  => 0.00,			-- 7-day avg cost for this route, regardless of account
//					       'route_cost_zval7'     => 2.00,			-- 7-day zval for this route, regardless of account
//					       'route_cost_avglast30' => 0.01,			-- 30-day avg cost for this route, regardless of account
//					       'route_cost_zval30'    => 1.04,			-- 30-day zval for this route, regardless of account 
//					       -1           => 0.00,				-- all cost to this route, regardless of account -- ONE DAY AGO
//					       -2           => 0.00,				-- all cost to this route, regardless of account -- TWO DAYS AGO
//					       -3           => 0.00,				-- all cost to this route, regardless of account -- THREE DAYS AGO
//					       -4           => 0.00,				-- all cost to this route, regardless of account -- FOUR DAYS AGO
//					       -5           => 0.00,				-- all cost to this route, regardless of account -- FIVE DAYS AGO
//					       -6           => 0.00,				-- all cost to this route, regardless of account -- SIX DAYS AGO
//					       -7           => 0.00,				-- all cost to this route, regardless of account -- SEVEN DAYS AGO
//					       'drilldown'  => array()				-- see frauddrilldown(), below
//					      )
//			        1470 => array ('routename'    => 'South Africa-Mobile',
//						... etc ...
//					      )
//			        ... etc ...
//			        ) // end of 'data' array
//	    ) // end of $result array
//
// Which might be presented to the user like:
//                                            Average Average                                                        
// Route Destination                    06-18   Last7  Last30   06-17   06-16   06-15   06-14   06-13   06-12   06-11
// ==================================================================================================================
// 1456  Sierra Leone Freetown         383.57    0.00    0.01    0.00    0.00    0.00    0.00    0.00    0.00    0.00
// 1470  South Africa-Mobile           241.02   62.31   70.32  145.19  110.62   73.34   11.39   24.54   12.19   58.91
// 1682  Italy-Mobile Vodafone          71.67   17.92   16.56   76.35    8.81   40.28    0.00    0.00    0.00    0.00
// 1646  UK-Mobile O2                   69.12   11.64   12.25   81.51    0.00    0.00    0.00    0.00    0.00    0.00
// 1137  Colombia-Mobile                64.37   30.69   32.36   49.63   28.95   34.79   28.12   27.80   19.11   26.40
//
////////////////////////////////////////////////////////////////////////////////////////////////

function fraudcheck($dt, $datelabel, $fraud_threshold=FRAUD_THRESHOLD, $fraud_zval_30day=FRAUD_ZVAL_30DAY, $fraud_zval_7day=FRAUD_ZVAL_7DAY) {
    GLOBAL $db_host, $db_db, $db_user, $db_pwd;
    GLOBAL $dbconn;
    GLOBAL $PHPFILE;

    // for better statistical results, don't include days we previously thought were fraud
    $excludestr30 = NULL;
    $excludestr7  = NULL;
    if (FRAUD_EXCLUDE_FRAUDDAYS) {
	$qu5 = "select date, route from xcf_results where isfraud=1 and date >= '$dt' - interval 30 day and date < '$dt'";
	$res5 = mysql_query($qu5);
	while ($row5 = mysql_fetch_array($res5)) {
	    $excludestr30 .= " and (not (date = '".$row5['date']."' and route=".$row5['route']."))";
	}
	$qu6 = "select date, route from xcf_results where isfraud=1 and date >= '$dt' - interval 7 day and date < '$dt'";
	$res6 = mysql_query($qu6);
	while ($row6 = mysql_fetch_array($res6)) {
	    $excludestr7 .= " and (not (date = '".$row6['date']."' and route=".$row6['route']."))";
	}
    }

    //echo "excludestr30 = $excludestr30\n";
    //echo "excludestr7 = $excludestr7\n";

    $trend30 = array();
    $trend7 = array();
    $result = array('today' => 0,
		    -1 => 0,
		    -2 => 0,
		    -3 => 0,
		    -4 => 0,
		    -5 => 0,
		    -6 => 0,
		    -7 => 0,
		    'data' => array());


    // report heading
    $qu7 = "select '$dt' as today,
		'$dt' - interval 1 day as min1,
		'$dt' - interval 2 day as min2,
		'$dt' - interval 3 day as min3,
		'$dt' - interval 4 day as min4,
		'$dt' - interval 5 day as min5,
		'$dt' - interval 6 day as min6,
		'$dt' - interval 7 day as min7";
    //echo "[".date('Y.m.d - H:i:s',time())."] fraudcheck: qu7 = $qu7\n";
    $q7 = mysql_query($qu7, $dbconn);
    $r7 = mysql_fetch_array($q7);
    $result['today'] = $r7['today'];
    $result[-1] = $r7['min1'];	// 1 day ago
    $result[-2] = $r7['min2'];	// 2 days ago
    $result[-3] = $r7['min3'];	// 3 days ago
    $result[-4] = $r7['min4'];	// 4 days ago
    $result[-5] = $r7['min5'];	// 5 days ago
    $result[-6] = $r7['min6'];	// 6 days ago
    $result[-7] = $r7['min7'];	// 7 days ago




    // 30-day average, etc
    $qu = "select route,
		max(cost)-min(cost)           as rangecost,
		avg(cost)                     as avgcost,
		stddev(cost)                  as stdcost
		from xcf_traffic_summary
		where date >= '$datelabel' - interval 30 day and date < '$datelabel'
		$excludestr30
		group by route
		order by route";
    //echo "[".date('Y.m.d - H:i:s',time())."] fraudcheck: qu = $qu\n";
    $q = mysql_query($qu, $dbconn);
    while ($r = mysql_fetch_array($q)) {
	$trend30[$r['route']] = $r;
    }

    // 7-day average, etc
    $qu = "select route,
		max(cost)-min(cost)           as rangecost,
		avg(cost)                     as avgcost,
		stddev(cost)                  as stdcost
		from xcf_traffic_summary
		where date >= '$datelabel' - interval 7 day and date < '$datelabel'
		$excludestr7
		group by route 
		order by route";
    //echo "[".date('Y.m.d - H:i:s',time())."] fraudcheck: qu = $qu\n";
    $q = mysql_query($qu, $dbconn);
    while ($r = mysql_fetch_array($q)) {
	$trend7[$r['route']] = $r;
    }

    $qu = "select route,
		sum(cost) as cost
		from xcf_traffic_summary
		where date = '$datelabel'
		group by route
		order by cost desc";
    //echo "[".date('Y.m.d - H:i:s',time())."] fraudcheck: qu = $qu\n";
    $q = mysql_query($qu, $dbconn);
    while ($r = mysql_fetch_array($q)) {
	if (isset($trend30[$r['route']]) || isset($trend7[$r['route']])) {

	    // 30 days 
	    $zval30_cost = 0;
	    if (round($trend30[$r['route']]['stdcost'],6) == 0) {
		$zval30_cost = 9999;	// standard deviation calculation over zero is, essentially, infinite
	    } elseif (isset($trend30[$r['route']]['stdcost'])) {
		$zval30_cost = round(($r['cost']-$trend30[$r['route']]['avgcost'])/$trend30[$r['route']]['stdcost'],4);
	    }

	    // 7 days 
	    $zval7_cost = 0;
	    if (round($trend7[$r['route']]['stdcost'],6) == 0) {
		$zval7_cost = 9999;	// standard deviation calculation over zero is, essentially, infinite
	    } elseif (isset($trend7[$r['route']]['stdcost'])) {
		$zval7_cost = round(($r['cost']-$trend7[$r['route']]['avgcost'])/$trend7[$r['route']]['stdcost'],4);
	    }



	    // For now, we only care about positive Z-values, we only care about cost, and only if the day's cost > the threshold ( $50 ).
	    if ($r['cost'] > $fraud_threshold)
		//echo "[".date('Y.m.d - H:i:s',time())."] route=".$r['route'].", fraudcheck: ".$r['cost']." >? $fraud_threshold :: $zval30_cost > $fraud_zval_30day :: $zval7_cost > $fraud_zval_7day\n";

		if ($r['cost'] > $fraud_threshold &&
		    ($zval30_cost > $fraud_zval_30day || $zval7_cost > $fraud_zval_7day)) {

		    $qu2 = "select routename from xcf_routes where route=".$r['route'];
		    //echo "[".date('Y.m.d - H:i:s',time())."] fraudcheck: qu2 = $qu2\n";
		    $q2 = mysql_query($qu2, $dbconn);
		    $r2 = mysql_fetch_array($q2);
		    $routename = (mysql_num_rows($q2) == 0 ? "?" : $r2['routename']);


		    $result['data'][$r['route']] = array('routename'            => $routename,
							 'route_cost'           => $r['cost'],
							 'route_cost_avglast7'  => (isset($trend7[$r['route']]['avgcost']) ? $trend7[$r['route']]['avgcost'] : 0),
							 'route_cost_zval7'     => $zval7_cost,
							 'route_cost_avglast30' => (isset($trend30[$r['route']]['avgcost']) ? $trend30[$r['route']]['avgcost'] : 0),
							 'route_cost_zval30'    => $zval30_cost,
							 'drilldown'    => array());
		    //'stddev7'  => (isset($trend7[$r['route']]['stdcost']) ? $trend7[$r['route']]['stdcost'] : 0),
		    //'stddev30' => (isset($trend30[$r['route']]['stdcost']) ? $trend30[$r['route']]['stdcost'] : 0));
		    for ($i=-1; $i>=-7; $i--) {
			$qu3 = "select cost from xcf_traffic_summary where route=".$r['route']." and date=substring('".$result[$i]."',1,10)";
			//echo "[".date('Y.m.d - H:i:s',time())."] fraudcheck: qu3 = $qu3\n";
			$q3 = mysql_query($qu3, $dbconn);
			$r3 = mysql_fetch_array($q3);
			$mincost = (mysql_num_rows($q3) == 0 ? 0.00 : $r3['cost']);
			$result['data'][$r['route']][$i] = $mincost;
		    }
		}
	}
    }


    // for each suspicious route, get suspicous-looking account information
    foreach ($result['data'] as $fck => $fcv) {
	echo "[".date('Y.m.d - H:i:s',time())."] fraudcheck: calling drilldown for date=$dt, datelabel=$datelabel, route=$fck\n";
	$result['data'][$fck]['drilldown'] = frauddrilldown($dt, $datelabel, $fck, $fcv);
    }

    return($result);
}




////////////////////////////////////////////////////////////////////////////////////////////////
// Take date, ROUTE, and SUMMARY information generated in fraudcheck(), above, and return the drilldown-by-account information:
//
//	array('account'  => array('account_calls'         => number of calls, by account
//				  'account_cost'        => cost of calls, by account
//				  'account_minutes'     => billed minutes, by account
//				  'account_billedamt'   => billed amount, by account
//				  'account_pctcost'     => of all the accounts that sent traffic to routeid, what percentage was this account?
//				  'account_maxcalldate' => GMT! -- most recent call from this account to the given routeid, to determine if fraud is still ongoing
//	)
////////////////////////////////////////////////////////////////////////////////////////////////
function frauddrilldown($dt, $datelabel, $route, $summarr) {
    GLOBAL $db_host, $db_db, $db_user, $db_pwd;
    GLOBAL $dbconn;
    GLOBAL $PHPFILE;

    $result = array();

    // sometimes the drilldown takes a very long time.  Let's see if it's because of DB contention
    $qu11 = "show full processlist";
    echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: ================================\n";
    echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: qu11 = $qu11\n";
    $q11 = mysql_query($qu11, $dbconn);
    while ($r11 = mysql_fetch_array($q11)) {
	//echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: ".print_r($r11,true)."\n";
	echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: Id=".$r11['Id'].", User=".$r11['User'].", Host=".$r11['Host'].", db=".$r11['db'].", Command=".$r11['Command'].", Time=".$r11['Time'].", State=".$r11['State'].", Info=".$r11['Info']."\n";
    }
    echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: ================================\n";


    // drilldown
    //
    // NOTE: get unsuccessful calls too because the fraud ATTEMPTS may still be going on even if they are not completing.
    //
    $qu = "select 
		account,
		count(*) as calls,
		round(sum(cost),2) as cost,
		round(sum(minutes),1) as minutes,
		round(sum(billedamt),2) as billedamt
		from xcf_cdr
		where route = $route
		  and calldate  >= '$dt' - interval 1 day and calldate  < '$dt'
		group by account
		order by cost desc";
    //echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: qu = $qu\n";
    //echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: mysql_query start\n";
    $q = mysql_query($qu, $dbconn);
    //echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: mysql_query end\n";
    while ($r = mysql_fetch_array($q)) {

	////////////////////////////////////////////////////////////////////////////////////////////////
	//
	// EXCEPTION #1:  Known ROUTE/account destinations that can be ignored.
	//
	////////////////////////////////////////////////////////////////////////////////////////////////
	$qu6 = "select * from xcf_exceptions where route=$route and account='".$r['account']."'";
	//echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: qu6 = $qu6\n";
	$q6 = mysql_query($qu6, $dbconn);
	if (mysql_num_rows($q6) > 0) {
	    $r6 = mysql_fetch_array($q6);
	    if ($r['cost'] < $r6['max_cost_allowed']) {
		echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: EXCLUSION #1: route=$route, account=".$r['account']." was exempt: cost=".$r['cost']." < allowed=".$r6['max_cost_allowed']."\n";
		continue;
	    }
	}




	////////////////////////////////////////////////////////////////////////////////////////////////
	//
	// if this account's cost is >= FRAUD_DRILLDOWN_HIGHPCT, then consider this account suspicious, otherwise ignore it.
	// We don't care about accounts that didn't contribute a high percentage of the suspicious activity for this ROUTE.
	//
	////////////////////////////////////////////////////////////////////////////////////////////////

	$pctcost = ($summarr['route_cost'] == 0 ? 0 : round($r['cost'] * 100 / $summarr['route_cost'],1));
	if ($pctcost >= FRAUD_DRILLDOWN_HIGHPCT) {

	    $result[$r['account']] = array('account_calls'       => $r['calls'],
					   'account_cost'        => $r['cost'],
					   'account_minutes'     => $r['minutes'],
					   'account_billedamt'   => $r['billedamt'],
					   'account_pctcost'     => $pctcost,
					   'account_maxcalldate' => '');			// filled in below
	}
    }


    ////////////////////////////////////////////////////////////////////////////////////////////////
    // maxcalldate is attempting to capture if the fraud is still ongoing, regardless of when this
    // function is called. so maxcalldate must come from a separate query that isn't restricted by
    // END date.
    //
    // We could have done this in the previous loop, but it is more efficient to pass through the
    // xcf_cdr table only once, matching the entire list of suspected accounts.
    //
    // NOTE: get unsuccessful calls too because the fraud ATTEMPTS may still be going on even if they are not completing.
    // 
    ////////////////////////////////////////////////////////////////////////////////////////////////
    if (count($result) > 0) {
	$acctstr = "(";
	foreach ($result as $k => $v) {
	    if ($acctstr != "(") $acctstr .= ",";
	    $acctstr .= "'$k'";
	}
	$acctstr .= ")";
	$qu4 = "select account, max(calldate) as maxcalldate,
		    '$PHPFILE' as phpfile
		from xcf_cdr
		where route = $route
		  and account in $acctstr
		  and calldate >= '$dt' - interval 1 day
		group by account";
	//echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: qu4 = $qu4\n";
	$q4 = mysql_query($qu4, $dbconn);
	while ($r4 = mysql_fetch_array($q4)) {
	    $result[$r4['account']]['account_maxcalldate'] = $r4['maxcalldate']." GMT";
	}
    }

    //echo "[".date('Y.m.d - H:i:s',time())."] frauddrilldown: result[summary] = ".print_r($result,true)."\n";
    return($result);
}




////////////////////////////////////////////////////////////////////////////////////////////////
// In normal usage, users of this package would need to access their own account tables to
// get account history that might be useful to the fraud response team.  Here we just
// return auto-generated stuff so you can see how it works.
////////////////////////////////////////////////////////////////////////////////////////////////
function fraudjournals($acctid, $num) {

    $journals = array();
    for ($i=0; $i<$num; $i++) {
	$journals[$i] = array('date' => date("Y-m-d",time()-(60*60*24*$i)),
			      'who'  => "$acctid person $i",
			      'text' => ("A journal entry about account $acctid by person $i at date ".date("Y-m-d",time()-(60*60*24*$i))."."));
    }
    return($journals);
}




////////////////////////////////////////////////////////////////////////////////////////////////
// In normal usage, users of this package would need to access their own account tables to
// get account information that might be useful to the fraud response team.  Here we just
// return auto-generated stuff so you can see how it works.
////////////////////////////////////////////////////////////////////////////////////////////////
function fraudaccountinfo($acctid) {
    return(array('custname'    => "Customer $acctid",
		 'contactname' => "Contact Person",
		 'phone'       => "X-XXX-XXX-XXXX",
		 'email'       => "cp@$acctid.com"));
}
?>
